package com.example.chapter06.database;

import android.content.Context;
import android.util.Log;

import androidx.annotation.Nullable;
import androidx.room.Room;

import com.example.chapter06.entity.CartInfo;
import com.example.chapter06.entity.GoodsInfo;

import java.sql.*;
import java.util.*;
import java.util.concurrent.Executors;
import java.util.concurrent.ExecutorService;

public class DBManager {
    private static DBManager instance = null;
    private final CartDatabase cartDatabase;
    private final GoodsDatabase goodsDatabase;
    private final String CLOUD_DB_URL = "jdbc:mysql://112.124.63.147:3306/userdb";
    private final String DB_USER = "userdb";
    private final String DB_PASSWORD = "Bdbg2181";

    /**
     * 构造函数，初始化内存数据库并同步云端数据。
     *
     * @param context 应用上下文
     */
    private DBManager(Context context) {
        // 初始化内存数据库
        cartDatabase = Room.inMemoryDatabaseBuilder(context, CartDatabase.class).build();
        goodsDatabase = Room.inMemoryDatabaseBuilder(context, GoodsDatabase.class).build();
        syncDataFromCloud(); // 启动时同步数据
    }

    /**
     * 单例模式获取 DBManager 实例。
     *
     * @param context 应用上下文
     * @return DBManager 实例
     */
    public static DBManager getInstance(@Nullable Context context) {
        if (instance == null) {
            if (context == null) throw new IllegalStateException("Context is null");
            instance = new DBManager(context);
        }
        return instance;
    }

    /**
     * 获取购物车数据库实例。
     *
     * @return 购物车数据库实例
     */
    public CartDatabase getCartDatabase() {
        return cartDatabase;
    }

    /**
     * 获取商品数据库实例。
     *
     * @return 商品数据库实例
     */
    public GoodsDatabase getGoodsDatabase() {
        return goodsDatabase;
    }

    /**
     * 从云端同步数据到内存数据库。
     */
    public void syncDataFromCloud() {
        ExecutorService executor = Executors.newSingleThreadExecutor();
        executor.execute(() -> {
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                try (Connection connection = DriverManager.getConnection(CLOUD_DB_URL, DB_USER, DB_PASSWORD)) {
                    syncCartFromCloud(connection); // 同步购物车数据
                    syncGoodsFromCloud(connection); // 同步商品数据
                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                Log.e("DBManager", "找不到 MySQL 驱动: " + e.getMessage());
            } catch (SQLException e) {
                e.printStackTrace();
                Log.e("DBManager", "连接数据库时发生错误: " + e.getMessage());
            } catch (Exception e) {
                e.printStackTrace();
                Log.e("DBManager", "同步数据时发生未知错误: " + e.getMessage());
            }
        });
    }

    /**
     * 从云端同步购物车数据到内存数据库。
     *
     * @param connection 数据库连接
     * @throws SQLException 如果执行 SQL 语句时发生错误
     */
    private void syncCartFromCloud(Connection connection) throws SQLException {
        try {
            List<CartInfo> cartList = new ArrayList<>();
            String query = "SELECT * FROM CartInfo";
            try (PreparedStatement stmt = connection.prepareStatement(query);
                 ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    CartInfo cart = new CartInfo();
                    cart.setId(rs.getLong("id"));
                    cart.setGoodsId(rs.getLong("goodsId"));
                    cart.setCount(rs.getInt("count"));
                    cart.setUpdateTime(rs.getString("updateTime"));
                    cartList.add(cart);
                }
            }
            cartDatabase.cartDao().insertCartList(cartList); // 插入购物车数据到内存数据库
        } catch (SQLException e) {
            e.printStackTrace();
            Log.e("DBManager", "同步购物车数据时发生错误: " + e.getMessage());
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            Log.e("DBManager", "同步购物车数据时发生未知错误: " + e.getMessage());
            throw e;
        }
    }

    /**
     * 从云端同步商品数据到内存数据库。
     *
     * @param connection 数据库连接
     * @throws SQLException 如果执行 SQL 语句时发生错误
     */
    private void syncGoodsFromCloud(Connection connection) throws SQLException {
        try {
            List<GoodsInfo> goodsList = new ArrayList<>();
            String query = "SELECT * FROM GoodsInfo";
            try (PreparedStatement stmt = connection.prepareStatement(query);
                 ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    GoodsInfo goods = new GoodsInfo();
                    goods.setId(rs.getLong("id"));
                    goods.setName(rs.getString("name"));
                    goods.setDesc(rs.getString("desc"));
                    goods.setPrice(rs.getDouble("price"));
                    goods.setPicPath(rs.getString("picPath"));
                    goods.setPicRes(rs.getInt("picRes"));
                    goodsList.add(goods);
                }
            }
            goodsDatabase.goodsDao().insertGoodsList(goodsList); // 插入商品数据到内存数据库
        } catch (SQLException e) {
            e.printStackTrace();
            Log.e("DBManager", "同步商品数据时发生错误: " + e.getMessage());
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            Log.e("DBManager", "同步商品数据时发生未知错误: " + e.getMessage());
            throw e;
        }
    }
}
